package com.edu.hre.db.sql.recruit;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreJobVacancy;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.recruit.VacancyForm;
import com.edu.hre.struts.form.recruit.VacancyListForm;

public class VacancyDAOService implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "vacancyDAOService";
	private BaseService baseService  = null;

	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public List<VacancyForm> getListVacancyForm(VacancyListForm form, int limit, int offset){
		
		List<VacancyForm> listResult = new ArrayList<VacancyForm>();
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isJob = form.getSelectedJobTitle() != null && !"".equals(form.getSelectedJobTitle())
				&& !"0".equals(form.getSelectedJobTitle());
		
		boolean isMan = form.getManagerKeyWord() != null && !"".equals(form.getManagerKeyWord().trim());

		boolean isSta = form.getSelectedStatus() != null && !"".equals(form.getSelectedStatus())
				&& !"2".equals(form.getSelectedStatus());
		
		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());

		sql = "SELECT vac.id AS col1," +
					" job.job_title AS col2, " +
					" CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) AS col3, " +
					" vac.name AS col4, " +
					" vac.comment AS col5, " +
					" vac.no_of_positions AS col6, " +
					" vac.status AS col7 " +
					
					" FROM hre_job_vacancy AS vac " +
					" LEFT OUTER JOIN hre_job_title AS job ON vac.job_title_code = job.id " +
					" LEFT OUTER JOIN hre_employee AS emp ON vac.hiring_manager_id = emp.emp_number ";
		
		sql +=	" WHERE 1 = 1 ";
		
		if (isJob) {
			sql +=	" AND job.id = ? ";
			params.add(form.getSelectedJobTitle());
		}
		if (isMan) {
			sql +=	" AND CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') = ? ";
			params.add(form.getManagerKeyWord());
		}
		if (isSta) {
			sql +=	" AND vac.status = ? ";
			params.add(form.getSelectedStatus());
		}
		
		if (isName) {
			sql +=	" AND vac.name LIKE '%" + form.getNameKeyWord() + "%' ";
		}

		sql += " ORDER BY vac.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				VacancyForm vacInfo = new VacancyForm();

				vacInfo.setId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[1], "0")));
				vacInfo.setJobTitle(CommonUtils.normalizeObjectToString(objs[2], ""));
				vacInfo.setHiringManager(CommonUtils.normalizeObjectToString(objs[3], ""));
				vacInfo.setName(CommonUtils.normalizeObjectToString(objs[4], ""));
				vacInfo.setNoOfPositions(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[6], "0")));
				
				int stat = Integer.parseInt(CommonUtils.normalizeObjectToString(objs[7], "0"));
				String[] allStatus = {"Not Active", "Active"};
				vacInfo.setStatus(allStatus[stat]);

				listResult.add(vacInfo);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	public int countVacancyForm(VacancyListForm form){

		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isJob = form.getSelectedJobTitle() != null && !"".equals(form.getSelectedJobTitle())
				&& !"0".equals(form.getSelectedJobTitle());
		
		boolean isMan = form.getManagerKeyWord() != null && !"".equals(form.getManagerKeyWord().trim());

		boolean isSta = form.getSelectedStatus() != null && !"".equals(form.getSelectedStatus())
				&& !"2".equals(form.getSelectedStatus());
		
		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());

		sql = "SELECT COUNT(*) AS col1" +
					
					" FROM hre_job_vacancy AS vac " +
					" LEFT OUTER JOIN hre_job_title AS job ON vac.job_title_code = job.id " +
					" LEFT OUTER JOIN hre_employee AS emp ON vac.hiring_manager_id = emp.emp_number ";
		
		sql +=	" WHERE 1 = 1 ";
		
		if (isJob) {
			sql +=	" AND job.id = ? ";
			params.add(form.getSelectedJobTitle());
		}
		if (isMan) {
			sql +=	" AND CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') = ? ";
			params.add(form.getManagerKeyWord());
		}
		if (isSta) {
			sql +=	" AND vac.status = ? ";
			params.add(form.getSelectedStatus());
		}
		
		if (isName) {
			sql +=	" AND vac.name LIKE '%" + form.getNameKeyWord() + "%' ";
		}

		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
	
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return 0;
		}
	}
	
	public void deleteVacancies(VacancyListForm form){

		ArrayList<VacancyForm> info = (ArrayList<VacancyForm>) form.getAllVacancies();
		for (int i = 0; i < info.size(); i++) {

			try {

				VacancyForm vac = info.get(i);
				if (vac.isSelected()) { 
	
					baseService.deleteFirstPojo("id", Compare.EQUAL, vac.getId(), null, null, 0, HreJobVacancy.class);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
